package com.walleipt.corgi.api.common.dao.h2;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowCountCallbackHandler;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.walle.ak47.commons.utils.StringUtils;
import com.walle.ak47.commons.utils.json.GBuilder;
import com.walleipt.corgi.api.common.dao.Page;
import com.walleipt.corgi.api.utils.H2JdbcUtils;

public class H2SmartDao {

	private static Logger logger = LoggerFactory.getLogger(H2SmartDao.class);

	private static H2SmartDao instance = null ;
	static JdbcTemplate jdbcTemplate = new JdbcTemplate();
	
	static {
		instance = new H2SmartDao() ;
		jdbcTemplate.setDataSource( H2JdbcUtils.getDataSource() );
	}
	
	public H2SmartDao() {
		jdbcTemplate.setDataSource( H2JdbcUtils.getDataSource() );
	}

	public static H2SmartDao instance() {
		jdbcTemplate.setDataSource( H2JdbcUtils.getDataSource() );
        return instance;
    }
	
	public static void crateTable( String createTableSql ) {
		jdbcTemplate.execute( createTableSql );
	}
	
	
	public static boolean hashColumnName(String sql, String colName) {
		RowCountCallbackHandler rcch = new RowCountCallbackHandler();
		jdbcTemplate.query(sql, rcch);
		logger.debug("结果集中的列数量:" + rcch.getColumnCount());
		logger.debug("结果集中的行数量:" + rcch.getRowCount());
		logger.debug("结果集中的列名:");
		String[] str = rcch.getColumnNames();
		for (int i = 0; i < str.length; i++) {
			logger.debug(str[i]);
		}

		return false;
	}

	public static int save(String tableName, final Map<String, Object> attrs) {
		final List<Object> paras = new ArrayList<Object>();
		final StringBuilder sql = new StringBuilder();
		sql.append("insert into ").append(tableName).append("(");
		StringBuilder temp = new StringBuilder(") values(");
		for (Entry<String, Object> e : attrs.entrySet()) {
			String colName = e.getKey();
			if (paras.size() > 0) {
				sql.append(", ");
				temp.append(", ");
			}
			sql.append(colName);
			temp.append("?");
			paras.add(e.getValue());
		}
		sql.append(temp.toString()).append(")");

		logger.debug("Save->SQL:" + sql.toString());
		logger.debug("Save->Paras:" + GBuilder.toJson(attrs));

		KeyHolder keyHolder = new GeneratedKeyHolder();
		int size = 0;
		try {
			size = jdbcTemplate.update(new PreparedStatementCreator() {
				public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {

					PreparedStatement ps = connection.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
					for (int i = 1; i <= paras.size(); i++) {
						ps.setObject(i, paras.get(i - 1));
					}
					return ps;
				}
			}, keyHolder);
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		Integer generatedId = keyHolder.getKey().intValue();

		for (Entry<String, Object> entry : findById(tableName, generatedId).entrySet())
			attrs.put(entry.getKey(), entry.getValue());

		return size;
	}
	
	public static int batchDeleteByIds(String tableName, List<String> ids ) {
		
		int size = 0;
		
		final List<Object> paras = new ArrayList<Object>();
		
		final StringBuilder sql = new StringBuilder();
		sql.append( "delete from " ).append(tableName).append(" where id in (");
		for (String id : ids) {
			if (paras.size() > 0)
				sql.append(",");
			
			sql.append("?");
			paras.add( id ) ;
		}
		sql.append(")") ;
		
		logger.debug("batchDeleteByIds->SQL:" + sql.toString());
		logger.debug("batchDeleteByIds->Paras:{tableName:" + tableName + ",ids:" + ids + "}");

		
		try {
			size = jdbcTemplate.update(sql.toString(), new PreparedStatementSetter() {
				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					for (int i = 1; i <= paras.size(); i++) {
						ps.setObject(i, paras.get(i-1));
					}
				}
			});
		} catch (Exception ex) {
			ex.printStackTrace();
		}

		return size;
	}

	public static int deleteById(String tableName, Object id) {
		StringBuilder sql = new StringBuilder();
		sql.append("delete from ").append(tableName).append(" where ").append(" id=? ");

		logger.debug("DeleteById->SQL:" + sql.toString());
		logger.debug("DeleteById->Paras:{tableName:" + tableName + ",id:" + id + "}");

		int size = 0;
		try {
			size = jdbcTemplate.update(sql.toString(), id);
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return size;
	}

	public static int updateSkipNull(String tableName, final Map<String, Object> attrs) throws Exception {
		String primaryKey = "id";
		if (attrs.get(primaryKey) == null)
			throw new Exception("talbe=" + tableName + " primaryKey is null");

		final List<Object> paras = new ArrayList<Object>();
		final StringBuilder sql = new StringBuilder();
		sql.append("update ").append(tableName).append(" set ");
		for (Entry<String, Object> e : attrs.entrySet()) {
			String colName = e.getKey();
			Object val = e.getValue();
			if (!primaryKey.equalsIgnoreCase(colName) && val != null) {
				if (paras.size() > 0)
					sql.append(", ");
				sql.append(colName).append(" = ? ");
				paras.add(e.getValue());
			}
		}
		sql.append(" where ").append(primaryKey).append(" = ?");
		paras.add(attrs.get(primaryKey));

		logger.debug("Update->SQL:" + sql.toString());
		logger.debug("Update->Paras:" + GBuilder.toJson(attrs));

		int size = 0;
		try {
			size = jdbcTemplate.update(sql.toString(), new PreparedStatementSetter() {
				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					for (int i = 1; i <= paras.size(); i++)
						ps.setObject(i, paras.get(i - 1));
				}
			});
		} catch (Exception ex) {
			ex.printStackTrace();
		}

		Map<String, Object> result = findById(tableName, attrs.get(primaryKey));
		if (result == null) {
			attrs.clear();
		} else {
			for (Entry<String, Object> entry : result.entrySet())
				attrs.put(entry.getKey(), entry.getValue());
		}

		return size;
	}

	public static int update(String tableName, final Map<String, Object> attrs) throws Exception {
		String primaryKey = "id";
		if (attrs.get(primaryKey) == null)
			throw new Exception("talbe=" + tableName + " primaryKey is null");

		final List<Object> paras = new ArrayList<Object>();
		final StringBuilder sql = new StringBuilder();
		sql.append("update ").append(tableName).append(" set ");
		for (Entry<String, Object> e : attrs.entrySet()) {
			String colName = e.getKey();
			if (!primaryKey.equalsIgnoreCase(colName)) {
				if (paras.size() > 0)
					sql.append(", ");
				sql.append(colName).append(" = ? ");
				paras.add(e.getValue());
			}
		}
		sql.append(" where ").append(primaryKey).append(" = ?");
		paras.add(attrs.get(primaryKey));

		logger.debug("Update->SQL:" + sql.toString());
		logger.debug("Update->Paras:" + GBuilder.toJson(attrs));

		int size = 0;
		try {
			size = jdbcTemplate.update(sql.toString(), new PreparedStatementSetter() {
				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					for (int i = 1; i <= paras.size(); i++)
						ps.setObject(i, paras.get(i - 1));
				}
			});
		} catch (Exception ex) {
			ex.printStackTrace();
		}

		Map<String, Object> result = findById(tableName, attrs.get(primaryKey));
		if (result == null) {
			attrs.clear();
		} else {
			for (Entry<String, Object> entry : result.entrySet())
				attrs.put(entry.getKey(), entry.getValue());
		}

		return size;
	}

	public static Map<String, Object> findById(String tableName, Object id) {

		StringBuilder sql = new StringBuilder();
		sql.append("select * from ").append(tableName).append(" where ").append("Id=?");

		logger.debug("FindById->Sql:" + sql.toString());
		logger.debug("FindById->Paras:{tableName:" + tableName + ",id:" + id + "}");

		Map<String, Object> result = null;
		try {
			result = jdbcTemplate.queryForMap(sql.toString(), id);
		} catch (EmptyResultDataAccessException ex) {
			return null;
		} catch (Exception ex) {
			ex.printStackTrace();
		}

		return result;
	}

	public static List<Map<String, Object>> find(String sql, Object... paras) {

		sql = sql.toLowerCase().trim();
		sql = new StringBuffer(sql).toString();

		List<Map<String, Object>> result = null;

		try {

			logger.debug("Find->SQL:" + sql.toString());
			logger.debug("Find->Paras:" + GBuilder.toJson(paras));

			result = jdbcTemplate.queryForList(sql, paras);
		} catch (Exception ex) {
			ex.printStackTrace();
		}

		return result;
	}

	@SuppressWarnings({ "deprecation" })
	public static long count(String sql, Object... paras) {
		long count = 0;

		sql = sql.toLowerCase().trim();

		StringBuilder countSql = new StringBuilder();
		countSql.append("select count(0) from ");
		countSql.append(replaceFormatSqlOrderBy(StringUtils.split(sql, "from ")[1]));

		logger.debug( "Count->SQL:[" + countSql.toString()+"]");
		logger.debug( "Count->Paras:[" + GBuilder.toJson(paras)+"]");

		try {
			count = jdbcTemplate.queryForLong(countSql.toString(), paras);
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return count;
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	public static Page selectForPage(int pageNumber, int pageSize, String sql, Object... paras) {
		sql = sql.toLowerCase().trim();
		if (pageNumber < 1 )
			pageNumber = 1 ;
		if ( pageSize < 1 )
			pageSize = 10 ;

		long totalRow = 0;
		int totalPage = 0;
		totalRow = count(sql, paras);
		if (totalRow <= 0)
			return new Page(new ArrayList(0), pageNumber, pageSize, 0, 0);

		// 总页数算法
		totalPage = (int) (totalRow / pageSize);
		if (totalRow % pageSize != 0) {
			totalPage++;
		}

		int offset = pageSize * (pageNumber - 1);
		sql = new StringBuffer().append(sql).append(" limit ").append(offset).append(", ").append(pageSize).toString();

		List<Map<String, Object>> list = null;

		logger.debug( "PaginateForMysql->SQL:[" + sql+"]");
		logger.debug( "PaginateForMysql->Paras:[" + GBuilder.toJson(paras)+"]");

		try {
			list = jdbcTemplate.queryForList(sql, paras);
		} catch (Exception ex) {
			ex.printStackTrace();
		}

		return new Page(list, pageNumber, pageSize, totalPage, (int) totalRow);
	}

	protected static String replaceFormatSqlOrderBy(String sql) {
		sql = sql.replaceAll("(\\s)+", " ");
		int index = sql.toLowerCase().lastIndexOf("order by");
		if (index > sql.toLowerCase().lastIndexOf(")")) {
			String sql1 = sql.substring(0, index);
			String sql2 = sql.substring(index);
			sql2 = sql2.replaceAll(
					"[oO][rR][dD][eE][rR] [bB][yY] [\u4e00-\u9fa5a-zA-Z0-9_.]+((\\s)+(([dD][eE][sS][cC])|([aA][sS][cC])))?(( )*,( )*[\u4e00-\u9fa5a-zA-Z0-9_.]+(( )+(([dD][eE][sS][cC])|([aA][sS][cC])))?)*",
					"");
			return sql1 + sql2;
		}
		return sql;
	}
}
